package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.CategoryEntity;
import br.com.skytef.entity.DetailProductEntity;
import br.com.skytef.entity.OrderItemEntity;
import br.com.skytef.entity.OrderTransEntity;
import br.com.skytef.entity.PosEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class OrderTransDao {
	public Connection connection;

	public OrderTransDao() {
		this.connection = new ConnectionFactory().getConnection();
	}

	public OrderTransDao(HttpSession session) {
		this.connection = utils.recoverSessionDBConnection(session);
	}

	public OrderTransDao(Connection con) {
		this.connection = con;
	}

	public Connection getConnection() {
		return connection;
	}
	
	/*-----------------------------------------------------------------------------------------------
	 * Exec Select
	 -----------------------------------------------------------------------------------------------*/	
	public OrderTransEntity selectTran(String field, int data) throws SQLException {
		Connection conn = new ConnectionFactory().getConnection();
		PreparedStatement stmt = conn.prepareStatement(this.getQrySelectWhere(field));
		stmt.setInt(1, data);		
		return selectTran(stmt,conn);		
	}
	
	public OrderTransEntity selectTran(String field, String data) throws SQLException {
		Connection conn = new ConnectionFactory().getConnection();
		PreparedStatement stmt = conn.prepareStatement(this.getQrySelectWhere(field));
		stmt.setString(1, data);		
		return selectTran(stmt,conn);			
	}
	
	public OrderTransEntity selectTran(String field, double data) throws SQLException {
		Connection conn = new ConnectionFactory().getConnection();
		PreparedStatement stmt = conn.prepareStatement(this.getQrySelectWhere(field));
		stmt.setDouble(1, data);		
		return selectTran(stmt,conn);	
	}
			
	private OrderTransEntity selectTran(PreparedStatement stmt, Connection conn) throws SQLException {
		OrderTransEntity tran = null;
		try {
			conn.setAutoCommit(false);
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				tran = new OrderTransEntity();
				tran.setIdTrans(rs.getInt("idTran"));
				tran.setFk_idPedido(rs.getInt("fk_idPedido"));
				tran.setFk_idPos(rs.getInt("fk_idPos"));				
				tran.setFk_idUsuario(rs.getInt("fk_idUsuario"));
				tran.setData(rs.getDate("data"));
				tran.setHora(rs.getTime("hora"));
			}

			rs.close();
			stmt.close();
			conn.commit();
			conn.close();
			return tran;

		} catch (SQLException e) {
			conn.rollback();
			conn.close();
			throw new RuntimeException(e);
		}
	}
		
	public List<OrderTransEntity> selectAll() throws SQLException {
		List<OrderTransEntity> transactions = new ArrayList<OrderTransEntity>();
		OrderTransEntity tran;

		Connection conn = new ConnectionFactory().getConnection();
		try {
			conn.setAutoCommit(false);
			PreparedStatement stmt = conn.prepareStatement(this.getQrySelectAll());
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				tran = new OrderTransEntity();

				tran.setIdTrans(rs.getInt("idTran"));
				tran.setFk_idPedido(rs.getInt("fk_idPedido"));
				tran.setFk_idPos(rs.getInt("fk_idPos"));				
				tran.setFk_idUsuario(rs.getInt("fk_idUsuario"));
				tran.setData(rs.getDate("data"));
				tran.setHora(rs.getTime("hora"));

				transactions.add(tran);
			}

			rs.close();
			stmt.close();
			conn.commit();
			conn.close();
			return transactions;

		} catch (SQLException e) {
			conn.rollback();
			conn.close();
			throw new RuntimeException(e);
		}
	}
	/*-----------------------------------------------------------------------------------------------
	 * Exec Insert
	 -----------------------------------------------------------------------------------------------*/	
	public int insert(OrderTransEntity tran) throws Exception {
		int idItem = 0;
		try {
			// Insere o item
			PreparedStatement stmt1 = connection.prepareStatement(this.getQryInsert());

			stmt1.setInt(1, tran.getFk_idPedido());
			stmt1.setInt(2, tran.getFk_idPos());
			stmt1.setInt(3, tran.getFk_idUsuario());
			//stmt1.setDate(4, tran.getData());
			//stmt1.setTime(5, tran.getHora());

			stmt1.execute();
			stmt1.close();

			// retorna o id gerado
			PreparedStatement stmt2 = connection.prepareStatement("SELECT Last_insert_id() as id FROM pedidos_has_trans");
			ResultSet rs = stmt2.executeQuery();

			if (rs.next()) {
				idItem = rs.getInt("id");
			}
			stmt2.close();
		} catch (SQLException e) {
			throw new Exception(e);
		}
		return idItem;
	}
	/*-----------------------------------------------------------------------------------------------
	 * Exec Update/Insert
	 -----------------------------------------------------------------------------------------------*/	
	public void update(OrderTransEntity tran) throws Exception {
		try {
			// Insere o item
			PreparedStatement stmt1 = connection.prepareStatement(this.getQryUpdate());

			stmt1.setInt(1, tran.getIdTrans());
			stmt1.setInt(2, tran.getFk_idPedido());
			stmt1.setInt(3, tran.getFk_idPos());
			stmt1.setInt(4, tran.getFk_idUsuario());
			stmt1.setDate(5, tran.getData());
			stmt1.setTime(6, tran.getHora());
			stmt1.setInt(7, tran.getIdTrans());
			
			stmt1.execute();
			stmt1.close();
		} catch (SQLException e) {
			throw new Exception(e);
		}
	}	
	/*-----------------------------------------------------------------------------------------------
	 * Query
	 -----------------------------------------------------------------------------------------------*/

	private String getQryUpdate() {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE pedidos_has_trans ");
		sql.append("SET    idtrans = ?, ");
		sql.append("SET    fk_idpos = ?, ");
		sql.append("       fk_idpedido = ?, ");
		sql.append("       fk_idusuario = ?, ");
		sql.append("       data = ?, ");
		sql.append("       hora = ? ");
		sql.append("WHERE  idtrans = ?; ");
		
		return sql.toString();
	}
	
	private String getQryInsert() {
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO pedidos_has_trans ");
		//sql.append("            (idtrans, ");
		sql.append("            (fk_idpos, ");
		sql.append("             fk_idpedido, ");
		sql.append("             fk_idusuario, ");
		sql.append("             data, ");
		sql.append("             hora) ");
		sql.append("VALUES      (?,?,?,CURDATE(),CURTIME()); ");

		return sql.toString();
	}
	
	private String getQrySelectAll() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT * FROM   pedidos_has_trans; ");

		return sql.toString();
	}
	
	private String getQrySelectWhere(String field) {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT * FROM pedidos_has_trans ");
		sql.append("WHERE "+ field +" = ?; ");
		
		return sql.toString();
	}

}
